lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-09-30(腾工).sql

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
Create VIEW [dbo].[BView_CameraControlBookMainPhotographed]
AS
SELECT  tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍摄状态,
dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime) AS 摄影时间, 
Ordpg_ReservationPhotographyTime AS 摄影时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 录入备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Dispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_DispatchTime)) AS 安排时间,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookReservationFilmSelectionRemind')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookReservationFilmSelectionRemind
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookReservationFilmSelectionRemind]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookReservationFilmSelectionRemind]
AS
SELECT   
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Number,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_ReservationFilmSelectionTime)) AS 选片时间, 
dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionTime AS 选片时间查询
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookReservationLookDesignRemind')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookReservationLookDesignRemind
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookReservationLookDesignRemind]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookReservationLookDesignRemind]
AS
SELECT   
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Number,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_ReservationLookDesignTime)) AS 看设计时间,
dbo.tb_ErpOrderDigital.Ordv_ReservationLookDesignTime AS 看设计时间查询
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookReservationPhotographedRemind')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookReservationPhotographedRemind
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookReservationPhotographedRemind]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookReservationPhotographedRemind]
AS
SELECT   
dbo.tb_ErpOrder.Ord_DividedShop, Ordv_Number AS Ord_Number,
dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称
,Ordpg_Sights AS 拍摄名称
,dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) AS 拍摄时间
,dbo.fn_CheckUserIDGetUserName(Ordpg_Dispatcher) AS 安排者
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')  AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookReservationSelectDressRemind')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookReservationSelectDressRemind
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookReservationSelectDressRemind]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookReservationSelectDressRemind]
AS
SELECT   
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Number,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_ClothingStatus) AS 选礼服状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationClothingName) AS 选礼服负责人,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_ReservationClothingTime)) AS 选礼服时间,
dbo.tb_ErpOrderDigital.Ordv_ReservationClothingTime AS 选礼服时间查询
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderPhotoPrints') and name='Opps_ReworkRemark2')
begin
	alter table tb_ErpOrderPhotoPrints add Opps_ReworkRemark2  nvarchar(500) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderPhotoPrints', N'COLUMN',N'Opps_ReworkRemark2'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'返工备注2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderPhotoPrints', @level2type=N'COLUMN',@level2name=N'Opps_ReworkRemark2'
GO

if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderProductList') and name='OPlist_ReworkRemark2')
begin
	alter table tb_ErpOrderProductList add OPlist_ReworkRemark2  nvarchar(500) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderProductList', N'COLUMN',N'OPlist_ReworkRemark2'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'返工备注2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_ReworkRemark2'
GO


-------------------2015-09-26------------------------
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookReservationPhotographedRemind')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookReservationPhotographedRemind
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookReservationPhotographedRemind]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookReservationPhotographedRemind]
AS
SELECT   
tb_ErpOrdersPhotography.ID AS PID,
dbo.tb_ErpOrder.Ord_DividedShop, Ordv_Number AS Ord_Number,
dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称
,Ordpg_Sights AS 拍摄名称
,dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) AS 拍摄时间
,dbo.fn_CheckUserIDGetUserName(Ordpg_Dispatcher) AS 安排者
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')  AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityDigitalArrangeAnPaiWorkDigital')
BEGIN
	DROP VIEW [dbo].[BView_DoorCityDigitalArrangeAnPaiWorkDigital]
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityDigitalArrangeAnPaiWorkDigital]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityDigitalArrangeAnPaiWorkDigital]
AS
SELECT    
tb_ErpOrder.ID,
Ord_Type,
Ord_DividedShop,
Ord_Number,
Ordv_ViceNumber, 
Ordv_DigitalNumber,
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationEarlyRepairName) + ''/'' + dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationEarlyRepairTime)) AS 初修完成期限, 
dbo.tb_ErpOrderDigital.Ordv_ReservationEarlyRepairTime AS 初修完成期限查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationDesignerName) + ''/'' + dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationDesignerTime)) AS 设计完成期限, 
dbo.tb_ErpOrderDigital.Ordv_ReservationDesignerTime AS 设计完成期限查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationRefinementName) + ''/'' + dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationRefinementTime)) AS 精修完成期限,
dbo.tb_ErpOrderDigital.Ordv_ReservationRefinementTime AS 精修完成期限查询
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrderDigital.Ordv_ReservationEarlyRepairName <> '''') OR
(dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2'')) AND 
(dbo.tb_ErpOrderDigital.Ordv_ReservationDesignerName <> '''') OR
(dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2'')) AND 
(dbo.tb_ErpOrderDigital.Ordv_ReservationRefinementName <> '''')
' 
GO